package bdqn.com.cui.dao.impl;

import bdqn.com.cui.dao.CommodityDao;
import bdqn.com.cui.pojo.Commodity;
import bdqn.com.cui.pojo.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Commodityimpl extends BaseDao implements CommodityDao {
    @Override
    public List<Commodity> getsenect(int form, int page) {
        Connection conn=getconn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet=null;
        String sql="SELECT c_id,c_name,l.p_name,c_jiage,c_shangpintu,c_jianjie,c_repertory,u.u_name,`c_sales volume`,c_type FROM commodity c INNER JOIN product_cates l ON c.c_leicing=l.ID INNER JOIN `User` u ON u.u_id=c.u_name LIMIT ?,?";
        try {
            preparedStatement=conn.prepareStatement(sql);
            preparedStatement.setObject(1,form);
            preparedStatement.setObject(2,page);
            resultSet=preparedStatement.executeQuery();
            List<Commodity> list=new ArrayList<>();
            while (resultSet.next()){
                if (resultSet!=null){
                    Commodity commodity=new Commodity();
                    commodity.setId(resultSet.getInt(1));
                    commodity.setName(resultSet.getString(2));
                    commodity.setLeicing(resultSet.getString(3));
                    commodity.setJiage(resultSet.getInt(4));
                    commodity.setShangpintu(resultSet.getString(5));
                    commodity.setJianjie(resultSet.getString(6));
                    commodity.setRepertory(resultSet.getInt(7));
                    commodity.setUsername(resultSet.getString(8));
                    commodity.setSalesvolume(resultSet.getInt(9));
                    commodity.setType(resultSet.getInt(10));
                    list.add(commodity);
                }
            }
            return list;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            getclass(resultSet,preparedStatement,conn);
        }
    }

    @Override
    public int getcount() {
        Connection conn=getconn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet=null;
        String sql="SELECT COUNT(1) FROM commodity";
        try {
            preparedStatement=conn.prepareStatement(sql);
            resultSet= preparedStatement.executeQuery();
            if (resultSet!=null){
                while (resultSet.next()){
                    return resultSet.getInt(1);
                }
            }
            return 0;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            getclass(resultSet,preparedStatement,conn);
        }
    }

    @Override
    public List<Commodity> getidserent(String id) {
        Connection conn=getconn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet=null;
        String sql="SELECT c_id,c_name,l.p_name,c_jiage,c_shangpintu,c_jianjie,c_repertory,u.u_name,`c_sales volume`,c_type FROM commodity c INNER JOIN product_cates l ON c.c_leicing=l.ID INNER JOIN `User` u ON u.u_id=c.u_name WHERE c_name=?";
        try {
            preparedStatement=conn.prepareStatement(sql);
            preparedStatement.setObject(1,id);
            resultSet=preparedStatement.executeQuery();

            List<Commodity> list=new ArrayList<>();
            while (resultSet.next()){
                if (resultSet!=null){
                    Commodity commodity=new Commodity();
                    commodity.setId(resultSet.getInt(1));
                    commodity.setName(resultSet.getString(2));
                    commodity.setLeicing(resultSet.getString(3));
                    commodity.setJiage(resultSet.getInt(4));
                    commodity.setShangpintu(resultSet.getString(5));
                    commodity.setJianjie(resultSet.getString(6));
                    commodity.setRepertory(resultSet.getInt(7));
                    commodity.setUsername(resultSet.getString(8));
                    commodity.setSalesvolume(resultSet.getInt(9));
                    commodity.setType(resultSet.getInt(10));
                    list.add(commodity);
                }
            }
            return list;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            getclass(resultSet,preparedStatement,conn);
        }
    }

    @Override
    public int add(Commodity commodity) {
        String spl="INSERT INTO commodity(c_name,c_leicing,c_jiage,c_shangpintu,c_jianjie,c_repertory,u_name,`c_sales volume`,c_type)VALUES(?,?,?,?,?,?,?,?,?)";
        Object[] obj={commodity.getName(),commodity.getLeicing(),commodity.getJiage(),commodity.getShangpintu(),commodity.getJianjie(),commodity.getRepertory(),commodity.getUsername(),commodity.getSalesvolume(),commodity.getType()};
        return Crud(spl,obj);
    }

    @Override
    public int delete(String id) {
        String sql="DELETE FROM commodity WHERE c_id=?";
        Object[] obj= {id};
        return Crud(sql,obj);
    }

    @Override
    public int update(User user) {
        return 0;
    }

    @Override
    public int Typeupdate(String type, String tj) {
        String sql="UPDATE commodity SET c_type=? WHERE c_id=?";
        Object[] obj={type,tj};
        return Crud(sql,obj);
    }

    @Override
    public Map getxl() {
        Connection conn=getconn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet=null;
        Map map=new HashMap();
        String sql="SELECT c_name,`c_sales volume` FROM commodity ORDER BY `c_sales volume` DESC LIMIT 0,10";
        try {
            preparedStatement=conn.prepareStatement(sql);
            resultSet=preparedStatement.executeQuery();
            while (resultSet.next()){
                if (resultSet!=null){
                    map.put(resultSet.getString(1),resultSet.getInt(2));
                }
            }
            return map;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            getclass(resultSet,preparedStatement,conn);
        }
    }


    @Override
    public int useradd(User user) {
        return 0;
    }

    @Override
    public int zcselent(String zhanghao) {
        return 0;
    }

    @Override
    public List<Commodity> fenleiselect() {
        Connection conn=getconn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet=null;
        String sql="SELECT ID,p_name FROM product_cates WHERE grade=1";
        try {
            preparedStatement=conn.prepareStatement(sql);
            resultSet=preparedStatement.executeQuery();
            List<Commodity> list=new ArrayList();
            while (resultSet.next()){
                if (resultSet!=null){
                   Commodity commodity=new Commodity();
                    commodity.setId(resultSet.getInt(1));
                   commodity.setName(resultSet.getString(2));
                   list.add(commodity);
                }
            }
            return list;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            getclass(resultSet,preparedStatement,conn);
        }
    }
}
